package databaseAcces;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.swing.JOptionPane;

public class DisplayRequest {
	private Connection conecttion ;
	private Connexion conect ;
	

	/**
	 * Construct for establishing connection
	 *
	 * @exception SQLException
	 *
	 */

		public DisplayRequest() {

		conect = new Connexion();
		conecttion = conect.getConnexion();
		}
		/**
		*This method authorize to get 
		*all book products
		* 	*@return  L  de type LinkedList of books
		*/
		public LinkedList<String> getCandidatures (String specialisation ,String diplome) {
			try {
		       LinkedList<String> candidat = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM Candidat,Experience where Candidat.num_Ca = Experience.num_Ca and specialite LIKE '"+specialisation+"' and diplome LIKE '"+diplome+"'";
		    		// +		    		 		" Experience.specialite='"+specialisation+"' and Experience.diplome='"+diplome+"' " ;
		    	
			   if (conecttion == null)
				   return null;
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) {
			   		candidat.add(rs.getString("salaire_souhaite")); System.out.println(" salaire  : "+rs.getString(8));
			   		candidat.add(rs.getString("nom_Poste_Exerce")); System.out.println(" nom experience : "+rs.getString(15));
			   		candidat.add(rs.getString("specialite")); System.out.println(" specialite : "+rs.getString(17));
			   		candidat.add(rs.getString("domaine")); System.out.println("domaine : "+rs.getString(18));
			   		candidat.add(diplome);
			   		candidat.add(rs.getString("sexe")); System.out.println(" sexe : "+rs.getString(10));
			   		candidat.add(rs.getString("adresse")+" - "+rs.getString("telephone")+" - "+rs.getString("courriel"));
			   		//candidat.add(Double.toString(((Double.parseDouble(rs.getString(5))/100)*Double.parseDouble(rs.getString(3)))+Double.parseDouble(rs.getString(3))));
			   	  }
			   		st.close();
			   		return candidat;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}
			} catch (Exception ef) {
				JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
			}
			
			     return null;
		}

		public LinkedList<String> getInformations (String login ,String password) {
			try {
		       LinkedList<String> informations = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM Candidat,user where Candidat.num_user = user.id and login LIKE '"+login+"' and password LIKE '"+password+"'" ;
		    	
			   if (conecttion == null) 
				   return null;
		
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) {
			   		informations.add(rs.getString("nom_prenom")); System.out.println(" nom prenom  : "+rs.getString("nom_prenom"));
			   		informations.add(rs.getString("adresse")); System.out.println(" adresse : "+rs.getString("adresse"));
			   		informations.add(rs.getString("type")); System.out.println(" type : "+rs.getString("type"));
			   	  }
			   		st.close();
			   		return informations;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}
			} catch (Exception ef) {
				JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
			}
			
			     return null;
		}

		public LinkedList<String> getInformations_2 (String login ,String password) {
			try {
		       LinkedList<String> informations = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM user where login LIKE '"+login+"' and password LIKE '"+password+"'" ;
		    		 
			   if (conecttion == null) 
				   return null;
			   
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) {
			   		informations.add(rs.getInt("id") + "");  
			   		informations.add(rs.getString("login")); 
			   		informations.add(rs.getString("password")); 
			   		informations.add(rs.getString("type")); 
			   	  }
			   		st.close();
			   		return informations;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}
			} catch (Exception ef) {
				JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
			}
			
			     return null;
		}
		
		public Vector<String> getCandidature(Vector<String> parametre)
		{
				Vector<String> resul = new Vector<String>();
				try {
					
					   if (conecttion == null) 
					   {
						   System.out.println("connection null ");
						   	return null;
					   }
					   
					// *******************************************************//
					// *			LA TABLE CANDIDAT						 *//
					// *******************************************************//
					String nom_prenom = parametre.elementAt(0);
					String web_site = parametre.elementAt(1);
					String adresse  = parametre.elementAt(2);
					String telephone = parametre.elementAt(3);
					String courriel = parametre.elementAt(4);
					String date_naiss = parametre.elementAt(5);
					String salaire_souhaite = parametre.elementAt(6);
					String diplome = parametre.elementAt(7);
					String sexe = parametre.elementAt(8);	
				//	String description = parametre.elementAt(9);
					String language = parametre.elementAt(10);	
					String num_user = parametre.elementAt(11);

			        String query =  "SELECT * FROM Candidat where " + " Candidat.nom_prenom LIKE '"+nom_prenom+"' "+
			    			   "and Candidat.web_site LIKE '"+web_site+"' " + "and Candidat.adresse LIKE '"+adresse+"' "+
			    			   "and Candidat.telephone LIKE '"+telephone+"' " + "and Candidat.courriel LIKE '"+courriel+"' " + 
			    			   "and Candidat.date_naiss='"+date_naiss+"' " + "and Candidat.salaire_souhaite='"+salaire_souhaite+"' "+
			    			   "and Candidat.diplome LIKE '"+diplome+"' " +"and Candidat.sexe='"+sexe+"' "
			    			   +"and Candidat.language LIKE '"+language+"' " + "and Candidat.num_user='"+num_user+"'";
			        
				   	try
				   	{
			       		Statement st = conecttion.createStatement();
			   			ResultSet rs = st.executeQuery(query);
			   			
			   			while (rs.next()) 
			   			{
			   					resul.add(rs.getString("num_Ca"));
			   					resul.add(rs.getString("nom_prenom"));
			   					resul.add(rs.getString("web_site"));
			   					resul.add(rs.getString("adresse"));
			   					resul.add(rs.getString("telephone"));
			   					resul.add(rs.getString("courriel"));
			   					resul.add(rs.getString("date_naiss"));
			   					resul.add(rs.getString("salaire_souhaite"));
			   					resul.add(rs.getString("diplome"));
			   					resul.add(rs.getString("sexe"));
			   					resul.add(rs.getString("description"));
			   					resul.add(rs.getString("language"));
			   					resul.add(rs.getString("num_user"));
			   					
			   			
			   			}				
				   		st.close();
				
				   	}
				   	catch (SQLException se) 
				   	{
				   		System.out.println("Erreur de requete");
				   	}
				} 
				catch (Exception ef) 
				{
					JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
				}
			  
			  return resul;
		}
		public Vector<String> getCandidature(int num_Ca)
		{
				Vector<String> resul = new Vector<String>();
				try {
					
					   if (conecttion == null) 
					   {
						   System.out.println("connection null ");
						   	return null;
					   }
					   
					String query =  "SELECT * FROM Candidat where num_Ca=" + num_Ca;

				   	try
				   	{
			       		Statement st = conecttion.createStatement();
			   			ResultSet rs = st.executeQuery(query);
			   			
			   			while (rs.next()) 
			   			{
			   				resul.add(rs.getString("num_Ca"));
		   					resul.add(rs.getString("nom_prenom"));
		   					resul.add(rs.getString("web_site"));
		   					resul.add(rs.getString("adresse"));
		   					resul.add(rs.getString("telephone"));
		   					resul.add(rs.getString("courriel"));
		   					resul.add(rs.getString("date_naiss"));
		   					resul.add(rs.getString("salaire_souhaite"));
		   					resul.add(rs.getString("diplome"));
		   					resul.add(rs.getString("sexe"));
		   					resul.add(rs.getString("description"));
		   					resul.add(rs.getString("language"));
		   					resul.add(rs.getString("num_user"));
			   			
			   			}				
				   		st.close();
				
				   	}
				   	catch (SQLException se) 
				   	{
				   		System.out.println("Erreur de requete");
				   	}
				} 
				catch (Exception ef) 
				{
					JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
				}
			  
			  return resul;

			
		}
		public Vector<String> getCandidature_user(int num_user)
		{
				Vector<String> resul = new Vector<String>();
				try {
					
					   if (conecttion == null) 
					   {
						   	return null;
					   }
					   
					String query =  "SELECT * FROM Candidat where num_user=" + num_user;

				   	try
				   	{
			       		Statement st = conecttion.createStatement();
			   			ResultSet rs = st.executeQuery(query);
			   			
			   			while (rs.next()) 
			   			{
			   				resul.add(rs.getString("num_Ca"));
		   					resul.add(rs.getString("nom_prenom"));
		   					resul.add(rs.getString("web_site"));
		   					resul.add(rs.getString("adresse"));
		   					resul.add(rs.getString("telephone"));
		   					resul.add(rs.getString("courriel"));
		   					resul.add(rs.getString("date_naiss"));
		   					resul.add(rs.getString("salaire_souhaite"));
		   					resul.add(rs.getString("diplome"));
		   					resul.add(rs.getString("sexe"));
		   					resul.add(rs.getString("description"));
		   					resul.add(rs.getString("language"));
		   					resul.add(rs.getString("num_user"));
			   			
			   			}				
				   		st.close();
				
				   	}
				   	catch (SQLException se) 
				   	{
				   		System.out.println("Erreur de requete");
				   	}
				} 
				catch (Exception ef) 
				{
					JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
				}
			  
			  return resul;

			
		}
		

		
		
		public int getNum_Candidatures (Vector<String> parametre)
		{		
		    int num_Ca = -1;
		    String kq = getCandidature(parametre).get(0); 
		    
		    if (kq != "")
		    	num_Ca = Integer.parseInt(kq);	  	
		    
  			return num_Ca;
		}
		
	
		
		/**
		*This method authorize to get 
		*all CDs musik products
		* 	*@return  L  de type LinkedList of CDs musik
		*/
		public LinkedList<String> getPostes (String specialisation,String lieu_travail) {

		       LinkedList<String> postes = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM Poste" ;
		     
			   if (conecttion == null) 
				   return null;
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) {
			   		postes.add(rs.getString(2));// String s = rs.getString(2);
			   		postes.add(rs.getString(3)); //s = rs.getString(3);
			   		postes.add(rs.getString(4)); //s = rs.getString(4);
			   		postes.add(rs.getString(5)); //s = rs.getString(5);
			   		postes.add(rs.getString(6)); //s = rs.getString(6);
			   		postes.add(rs.getString(7)); //s = rs.getString(7);
			   		postes.add(getInformationsEntreprise(rs.getString("num_En")));
			   	  }
			   		st.close();
			   		return postes;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}

			     return null;
		}
		/**
		*This method authorize to get 
		*all software products
		* 	*@return  L  de type LinkedList of software products 
		*/
		public LinkedList<String> getExperienceCandiate () {

		       LinkedList<String> experience = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM Experience" ;
		     
			   if (conecttion == null) 
				   return null;
			  
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) {
			   		  String s = rs.getString(2);
			   		experience.add(rs.getString(2)); s = rs.getString(2);
			   		experience.add(rs.getString(3)); s = rs.getString(3);
			   		experience.add(rs.getString(4)); s = rs.getString(4);
			   		experience.add(rs.getString(5)); s = rs.getString(5);
			   		experience.add(rs.getString(6)); s = rs.getString(6);
			   		experience.add(rs.getString(7)); s = rs.getString(7);

			   	  }
			   		st.close();
			   		return experience;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}

			     return null;
		}
		
		
		public LinkedList<String> getExperienceCandiate (int num_Ca) {

		       LinkedList<String> experience = new LinkedList<String>();
		       String req = null; 
		    		 req = "SELECT * FROM Experience WHERE num_Ca=" + num_Ca  ;
		     
			   if (conecttion == null) 
				   return null;
			  
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) 
			   	  {
			   		experience.add(rs.getString("num_Ca")); 
			   		experience.add(rs.getString("num_experien")); 
			   		experience.add(rs.getString("nom_Poste_Exerce")); 
			   		experience.add(rs.getString("specialite")); 
			   		experience.add(rs.getString("domaine")); 	   		
			   	  }
			   		st.close();
			   		return experience;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}

			     return null;
		}
		public String getInformationsEntreprise (String num_En) {

		       String experience="";
		       String req = "SELECT * FROM Entreprise WHERE num_En=" + num_En  ;
		     
			   if (conecttion == null) return null;
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   	  while (rs.next()) 
			   	  {
			   		  experience = rs.getString("web_site")+" - "+rs.getString("adresse")
			   				  +" - "+rs.getString("courriel");
			   		   		
			   	  }
			   		st.close();
			   		return experience;
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}

			     return null;
		}
		public int verifyUser (String login,String password, String type) {

		       String req = null; 
		    		 req = "SELECT * FROM user Where login LIKE'"+login+"' and  password LIKE'"+password+"'" ;
		     
			   if (conecttion == null) return 0;
			   	try {
			   		Statement st = conecttion.createStatement();
			   		ResultSet rs = st.executeQuery(req);
			   		
			   	  while (rs.next()) {
			   		String s1 = rs.getString(2);
			   		String s2 = rs.getString(3);
			   		  if (rs.getString(2).equals(login) && (rs.getString(3).equals(password))) {
			   			  st.close();
			   			  return 0; // Cela veut dire que le user existe déjà
			   		  }    			 
			   		
			   	  }
			   	 if (!type.equals("2")) {
			   		req = "INSERT INTO user(login,password,type) VALUES('"+login+"','"+password+"','"+type+"')";
	   			  	
	   			  		st = conecttion.createStatement();
	   			  		st.executeUpdate(req);
			   	 }
	   			  		st.close();
	   			  	
	   			  return 1;
			   		  			   	  
			   	}
			   	catch (SQLException se) {
			   		System.out.println("Erreur de requete");
			   	}

			     return 0;
		}
		
		// The Anh
		
		// recuper les infos pour Company en basant sur le numero user
		public Vector<String> getCompany_user(int num_user)
		{
			Vector<String> resul = new Vector<String>();
			try {
				
				   if (conecttion == null) 
				   {
					   	return null;
				   }
				   
				String query =  "SELECT * FROM Entreprise where num_user=" + num_user;

			   	try
			   	{
		       		Statement st = conecttion.createStatement();
		   			ResultSet rs = st.executeQuery(query);
		   			
		   			while (rs.next()) 
		   			{
		   				resul.add(rs.getString("num_En"));		   				
	   					resul.add(rs.getString("nom_En"));
	   					resul.add(rs.getString("web_site"));
	   					resul.add(rs.getString("adresse"));
	   					resul.add(rs.getString("telephone"));
	   					resul.add(rs.getString("courriel"));	   					
	   					resul.add(rs.getString("description"));
	   					resul.add(rs.getString("num_user"));
		   			
		   			}				
			   		st.close();
			
			   	}
			   	catch (SQLException se) 
			   	{
			   		System.out.println("Erreur de requete");
			   	}
			} 
			catch (Exception ef) 
			{
				JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
			}
		  
		  return resul;

		}
		
		// recuper les infos de Poste en basant sur le numero de Company
		public Vector<String> getPoste_num_En(int num_En)
		{
			Vector<String> resul = new Vector<String>();
			try {
				
				   if (conecttion == null) 
				   {
					   	return null;
				   }
				   
				String query =  "SELECT * FROM Poste where num_En=" + num_En;

			   	try
			   	{
		       		Statement st = conecttion.createStatement();
		   			ResultSet rs = st.executeQuery(query);
		   			
		   			while (rs.next()) 
		   			{
		   				resul.add(rs.getInt("num_Po") +"");	
		   				resul.add(rs.getString("titre"));		   				
	   					resul.add(rs.getString("specialite"));
	   					resul.add(rs.getString("domaine"));   						
	   					resul.add(rs.getString("salaire_propose")); 					
	   					resul.add(rs.getString("experience_requis"));
	   					resul.add(rs.getString("lieu_travail"));	   					
	   					resul.add(rs.getString("niveau"));
	   					resul.add(rs.getString("description"));	   					
	   					resul.add(rs.getInt("num_En")+"");
	   					resul.add(rs.getString("date_limite"));
		   			
		   			}				
			   		st.close();
			
			   	}
			   	catch (SQLException se) 
			   	{
			   		System.out.println("Erreur de requete");
			   	}
			} 
			catch (Exception ef) 
			{
				JOptionPane.showMessageDialog(null,"Probleme de récupération"+ef.getMessage());
			}
		  
		  return resul;


		}
		// Fin The Anh
		
}
